package com.ssy.lingxi.report.dao.merchantmember;

import com.ssy.lingxi.common.utils.DateUtil;
import com.ssy.lingxi.report.model.bo.MemberBO;
import com.ssy.lingxi.report.model.bo.MemberRoleBO;
import com.ssy.lingxi.report.model.bo.StatisticsMemberBO;
import com.ssy.lingxi.report.model.bo.StatisticsNewlyAddedBO;
import com.ssy.lingxi.report.model.bo.tally.statistics.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.time.LocalDateTime;
import java.util.List;

/**
 * 会员服务
 * @author 伍将
 * @date 2020/11/4
 * @version 2.0.0
 */
@Service
public class MemberDAO {

    @Autowired
    @Qualifier("merchantMemberJdbcTemplate")
    private JdbcTemplate jdbcTemplate;


    /**
     * 查询会员列表
     * @author 伍将
     * @date 2020/11/4
     * @param memberIds:
     * @return java.util.List<com.ssy.lingxi.report.model.bo.MemberBO>
     **/
    public List<MemberBO> getMemberList(List<Long> memberIds) {
        if (memberIds == null || memberIds.size() <= 0) {
            return null;
        }

        String tempIds = org.apache.commons.lang.StringUtils.join(memberIds.toArray(), ",");
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ")
                .append(" id,name,logo")
                .append(" FROM ")
                .append(" ms_mc_member ")
                .append(" where id in(" + tempIds + " )");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(MemberBO.class));
    }

    /**
     * 获取按日统计注册会员数据【统计平台】 todo ds 待产品确认
     * @author 伍将
     * @date 2020/11/9
     * @param startTime:
     * @param endTime:
     * @return java.util.List<com.ssy.lingxi.report.model.bo.StatisticsMemberBO>
     **/
    public List<StatisticsMemberBO> getStatisticsMemberByDay(LocalDateTime startTime, LocalDateTime endTime) {

        // 新增平台会员审核通过

        StringBuilder sql = new StringBuilder();
        sql.append("select to_char(create_time ,'YYYY-MM-DD') date_time,sub_role_name roleName,count(*) count ")
                .append("from ms_mc_member_relation ")
                .append("where rel_type = 0 ")
                .append("and create_time between '" + DateUtil.formatLocalDateTimeToStr(startTime) + "' and '" +  DateUtil.formatLocalDateTimeToStr(endTime) + "' ")
                .append("group by date_time,sub_role_name");
//                .append(" select date_time,role_name,sum(num) count from(")
//                .append(" 	select to_char(r.create_time,'YYYY-MM-DD') date_time,b.role_name,count(r.id) num")
//                .append(" 	from ms_mc_member_relation r")
//                .append(" 	inner join ms_mc_role b")
//                .append(" 	on r.lower_role_id=b.id")
//                .append(" 	where rel_type =0")
//                .append(" 	and inner_status>=1")
//                .append("   and r.create_time between '" + startTime + "' and '" + endTime + "'")
//                .append("   and rel_source != 4 and rel_source != 5")
//                .append(" 	group by date_time,b.role_name")
//                .append(" 	union all")
//                .append(" 	select to_char(mmmivh.create_time,'YYYY-MM-DD') date_time,r.role_name,count(r.id) num ")
//                .append(" 	from ms_mc_member_inner_validate_history mmmivh")
//                .append(" 	inner join ms_mc_role r")
//                .append(" 	on mmmivh.sub_role_id=r.id")
//                .append(" 	where operate_enum = 3 ")
//                .append("   and mmmivh.create_time between '" + startTime + "' and '" + endTime + "'")
//                .append(" 	and (member_id,role_id) not in (select member_id, role_id from ms_mc_member_relation where rel_type =0 limit 1)")
//                .append(" 	group by date_time,r.role_name")
//                .append(" )t")
//                .append(" group by date_time,role_name;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(StatisticsMemberBO.class));
    }

    /**
     * 获取按月统计会员数据
     * @author 伍将
     * @date 2020/11/9
     * @param startTime:
     * @param endTime:
     * @return java.util.List<com.ssy.lingxi.report.model.bo.StatisticsMemberBO>
     **/
    public List<StatisticsMemberBO> getStatisticsMemberByMonth(LocalDateTime startTime, LocalDateTime endTime) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select to_char(date_time,'YYYY-MM-01') date_time,role_name,count(id)")
                .append(" from rep_member_day_report ")
                .append(" where date_time between '" + startTime + "' and '" + endTime + "'")
                .append(" group by to_char(date_time,'YYYY-MM-01'),role_name;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(StatisticsMemberBO.class));
    }

    /**
     * 获取角色名称列表
     * @author 伍将
     * @date 2020/11/10
     * @return java.util.List<java.lang.String>
     **/
    public List<MemberRoleBO> getRoleNameList() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select distinct role_name from ms_mc_role")
                .append(" where status=1;");    // status为1，代表有效

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(MemberRoleBO.class));
    }

    /**
     * 统计新增会员 //TODO 待修改
     * @author 伍将
     * @date 2020/11/12
     * @param startTime:
     * @param endTime:
     * @return java.util.List<com.ssy.lingxi.report.model.bo.StatisticsNewlyAddedBO>
     **/
    public List<StatisticsNewlyAddedBO> getNewlyAddedMemberByDay(LocalDateTime startTime, LocalDateTime endTime) {
        StringBuilder sql = new StringBuilder();

        sql.append(" select date_time,sum(num) count from(")
                .append(" 	select to_char(create_time,'YYYY-MM-DD') date_time,count(*) num ")
                .append("   from ms_mc_member_relation ")
                .append("   where rel_type = 0 and rel_source in (0,1,2,3) ")
                .append("   and create_time between '" + DateUtil.formatLocalDateTimeToStr(startTime) + "' and '" + DateUtil.formatLocalDateTimeToStr(endTime) + "'")
                .append(" 	group by date_time ")
                .append("  union all ")
                .append("   select to_char(create_time,'YYYY-MM-DD') date_time,count(*) num ")
                .append("   from ms_mc_member_inner_validate_history ")
                .append("   where operation ='提交平台审核' ")
                .append("   and create_time between '" + DateUtil.formatLocalDateTimeToStr(startTime) + "' and '" + DateUtil.formatLocalDateTimeToStr(endTime) + "'")
                .append(" 	group by date_time ")
                .append(" ) t")
                .append(" group by date_time;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(StatisticsNewlyAddedBO.class));
    }

    /**
     * 查询会员统计数量
     * @return
     */
    public List<MemberStatisticsTallyVO> getStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select member_id,role_id,")
                .append(" sum(case when inner_status = 1 then 1 else 0 end) toBeCommitValifyCount,")
                .append(" sum(case when inner_status = 2 then 1 else 0 end) toBeValifyStep1Count,")
                .append(" sum(case when inner_status = 3 then 1 else 0 end) toBeValifyStep2Count,")
                .append(" sum(case when inner_status in(4,5) then 1 else 0 end) tobeConfirmCount")
                .append(" from ms_mc_member_relation")
                .append(" where task_id<>''")
                .append(" group by member_id,role_id;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(MemberStatisticsTallyVO.class));
    }

    /**
     * 会员入库统计
     * @return
     */
    public List<MemberImportStatisticsTallyVO> getImportCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select member_id,role_id,")
                .append(" sum(case when inner_status = 1 then 1 else 0 end) toBeValifyInfo,")
                .append(" sum(case when inner_status = 3 then 1 else 0 end) toBeValifyQualification,")
                .append(" sum(case when inner_status = 5 then 1 else 0 end) toBeImportExplore,")
                .append(" sum(case when inner_status = 7 then 1 else 0 end) toBeImportClassify,")
                .append(" sum(case when inner_status = 9 then 1 else 0 end) toBeValifyStep1,")
                .append(" sum(case when inner_status = 11 then 1 else 0 end) toBeValifyStep2,")
                .append(" sum(case when inner_status in(2,4,6,8,10,12,13) then 1 else 0 end) toBeConfirm")
                .append(" from ms_mc_member_relation")
                .append(" where rel_type=1 and outer_status =5")
                .append(" group by member_id,role_id;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(MemberImportStatisticsTallyVO.class));
    }

    /**
     * 会员变更统计
     * @return
     */
    public List<MemberChangeStatisticsTallyVO> getChangeCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select member_id,role_id,")
                .append(" sum(case when inner_status = 16 then 1 else 0 end) toBeValifyStep1,")
                .append(" sum(case when inner_status = 18 then 1 else 0 end) toBeValifyStep2,")
                .append(" sum(case when inner_status in(17,19,20) then 1 else 0 end) toBeConfirm")
                .append(" from ms_mc_member_relation  where rel_type=1 ")
                .append(" group by member_id,role_id;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(MemberChangeStatisticsTallyVO.class));
    }

    /**
     * 会员考评[待发布考评表、待提交汇总考评结果、待审核考评结果(一级)、待审核考评结果(二级)、待通报考评结果]
     * 不含【待考评打分】
     * @return
     */
    private List<MemberKpiStatisticsTallyVO> getKpiCountPart1Of2() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select member_id,role_id,")
                .append(" sum(case when status = 1 then 1 else 0 end) toBePublishCount,")
                .append(" sum(case when status in (3,5,7) then 1 else 0 end) toBeCommitCount,")
                .append(" sum(case when status = 4 then 1 else 0 end) toBeValifyStep1Count,")
                .append(" sum(case when status = 6 then 1 else 0 end) toBeValifyStep2Count,")
                .append(" sum(case when status = 8 then 1 else 0 end) toBeNoticeCount")
                .append(" from ms_mc_member_appraisal ")
                .append(" group by member_id,role_id;");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(MemberKpiStatisticsTallyVO.class));
    }

    /**
     * 会员考评[待考评打分]
     * @return
     */
    private List<MemberKpiStatisticsTallyVO> getKpiCountPart2Of2(Long userId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select a.member_id,a.role_id,")
                .append(" count(*) toBeScoringCount")
                .append(" from ms_mc_member_appraisal a")
                .append(" left join ms_mc_member_appraisal_item i on a.id=i.appraisal_id")
                .append(" inner join ms_mc_member_user u on i.user_id=u.id")
                .append(" where a.status= i.status=0 and i.send_appraisal=1 and u.id="+userId)
                .append(" group by a.member_id,b.role_id");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(MemberKpiStatisticsTallyVO.class));
    }

    /**
     * 会员考评
     * @return
     */
    public List<MemberKpiStatisticsTallyVO> getKpiCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select member_id,role_id,")
                .append(" sum(case when status = 1 then 1 else 0 end) toBePublishCount,")
                .append(" sum(case when status = 2 then 1 else 0 end) toBeScoringCount,")
                .append(" sum(case when status in (3,5,7) then 1 else 0 end) toBeCommitCount,")
                .append(" sum(case when status = 4 then 1 else 0 end) toBeValifyStep1Count,")
                .append(" sum(case when status = 6 then 1 else 0 end) toBeValifyStep2Count,")
                .append(" sum(case when status = 8 then 1 else 0 end) toBeNoticeCount")
                .append(" from ms_mc_member_appraisal ")
                .append(" group by member_id,role_id;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(MemberKpiStatisticsTallyVO.class));
    }

    /**
     * 会员整改
     * @return
     */
    public List<MemberRectifyStatisticsTallyVO> getRectifyCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select member_id,role_id,")
                .append(" sum(case when outer_status = 1 then 1 else 0 end) toBeSendNoticeCount,")
                .append(" sum(case when outer_status = 3 then 1 else 0 end) toBeConfirmNoticeCount")
                .append(" from ms_mc_member_rectify ")
                .append(" group by member_id,role_id;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(MemberRectifyStatisticsTallyVO.class));
    }

    /**
     * 会员整改通知
     * @return
     */
    public List<MemberRectifyNoticeStatisticsTallyVO> getRectifyNoticeCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select sub_member_id memberId,sub_role_id roleId,")
                .append(" sum(case when outer_status = 2 then 1 else 0 end) toBeRectifyCount")
                .append(" from ms_mc_member_rectify ")
                .append(" group by sub_member_id,sub_role_id;");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(MemberRectifyNoticeStatisticsTallyVO.class));
    }

    /**
     * 平台会员审核
     * @return
     */
    public List<PlatformMemberVerifyStatisticsTallyVO> getPlatformMemberVerifyCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select member_id,role_id,")
                .append(" sum(case when inner_status = 1 then 1 else 0 end) toBeCommitValifyCount,")
                .append(" sum(case when inner_status = 3 then 1 else 0 end) toBeValifyStep1Count,")
                .append(" sum(case when inner_status = 5 then 1 else 0 end) toBeValifyStep2Count,")
                .append(" sum(case when inner_status in (2,4,6,7) then 1 else 0 end) tobeConfirmCount")
                .append(" from ms_mc_member_relation where rel_type=0")
                .append(" group by member_id,role_id;");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PlatformMemberVerifyStatisticsTallyVO.class));
    }
}
